package maa.dao.h2;

import maa.dao.DAOException;
import maa.dao.DAOManager;
import maa.dao.CourseDAO;
import maa.dao.DAOCommand;
import maa.entity.Course;
import maa.entity.Gender;
import maa.entity.Mentor;
import maa.entity.Role;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class CourseDAOimpl implements DAOCommand, CourseDAO {
    private static final Logger logger = LoggerFactory.getLogger(CourseDAOimpl.class);
    private final String CREATE_COURSE = "INSERT INTO courses(courseID, name, description, monthlyprice, mentor) VALUES(?, ?, ?, ?, ?)";
    private final String FIND_ALL = "SELECT * from courses join mentors on courses.MENTOR=mentors.mentorid join users on mentors.mentorid = users.userid";
    private final String DELETE = "DELETE FROM courses WHERE courseid = ?";
    private final String FIND_BY_ID = "SELECT * FROM COURSES join mentors on courses.mentor=mentors.mentorid join users on mentors.mentorid = users.userid WHERE COURSEID=?";
    private final String UPDATE_COURSE = "UPDATE courses set name = ?, description = ?, monthlyprice = ? where courseid = ?";
    private final String SET_MENTOR = "UPDATE courses set mentor = ? where courseid = ?";
    private Connection connection;

    public CourseDAOimpl() {
    }

    public CourseDAOimpl(Connection connection) {
        this.connection = connection;
    }

    public boolean create(Course course) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(CREATE_COURSE);
                preparedStatement.setObject(1, course.getUuid());
                preparedStatement.setString(2, course.getCourseName());
                preparedStatement.setString(3, course.getCourseDescription());
                preparedStatement.setDouble(4, course.getCourseMonthlyPrice());
                preparedStatement.setObject(5, course.getMentor().getUuid());

                res = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                logger.error("Could not create course " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) return true;
        else return false;
    }

    public boolean setMentor(UUID course_uuid, UUID mentor_uuid) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(SET_MENTOR);
                preparedStatement.setObject(1, mentor_uuid);
                preparedStatement.setObject(2, course_uuid);
                res = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                logger.error("Could not set mentor " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) return true;
        else return false;
    }

    public boolean update(Course course) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(UPDATE_COURSE);
                preparedStatement.setString(1, course.getCourseName());
                preparedStatement.setString(2, course.getCourseDescription());
                preparedStatement.setDouble(3, course.getCourseMonthlyPrice());
                preparedStatement.setObject(4, course.getUuid());
                res = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                logger.error("Could not update course " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) return true;
        else return false;
    }

    public boolean delete(UUID uuid) throws DAOException {
        int res = 0;
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(DELETE);
                preparedStatement.setObject(1, uuid);
                res = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                logger.error("Could not delete course " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        if (res > 0) return true;
        else return false;
    }

    @Override
    public Object execute(DAOManager daoManager) {
        return new Object();
    }

    public List<Course> findAll() throws DAOException {
        List<Course> list = new ArrayList<>();
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(FIND_ALL);
                ResultSet set = preparedStatement.executeQuery();
                while (set.next()) {
                    Course course = createEntity(set);
                    list.add(course);

                }
            } catch (SQLException e) {
                logger.error("Could not find all courses " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        return list;
    }

    public void close(PreparedStatement statement) {
        try {
            statement.close();
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
    }

    private Course createEntity(ResultSet set) {
        Course course = new Course();
        Mentor mentor = new Mentor();

        try {
            mentor.setUuid((UUID) set.getObject("mentorid"));
            mentor.setInfo(set.getString("info"));
            mentor.setRang(set.getString("rang"));
            mentor.setWorkExperience(set.getDouble("work_experience"));
            mentor.setEmail(set.getString("email"));
            mentor.setPassword(set.getString("password"));
            mentor.setName(set.getString("username"));
            mentor.setSurname(set.getString("surname"));
            mentor.setBirthdate(set.getDate("birthdate"));
            mentor.setRole(Role.valueOf(set.getString("role")));
            mentor.setGender(Gender.valueOf(set.getString("gender")));

            course.setUuid((UUID) set.getObject("courseid"));
            course.setCourseName(set.getString("name"));
            course.setCourseDescription(set.getString("description"));
            course.setCourseMonthlyPrice(set.getDouble("monthlyprice"));
            course.setMentor(mentor);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return course;
    }

    public List<Course> findCoursesById(List<UUID> uuids) throws DAOException {
        List<Course> courseList = new ArrayList<>();
        for (UUID uuid : uuids) {
            courseList.add(findById(uuid));
        }
        return courseList;
    }

    public Course findById(UUID uuid) throws DAOException {
        Course course = new Course();
        if (connection != null && uuid != null) {
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(FIND_BY_ID);
                preparedStatement.setObject(1, uuid);
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    course = createEntity(resultSet);
                }
            } catch (SQLException e) {
                logger.error("Could not find course by id " + e.getMessage());
                throw new DAOException(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    close(preparedStatement);
                }
            }
        } else {
            throw new DAOException("No connection available");
        }
        return course;
    }
}
